package com.yunzheng.studyLeaveSystem.modules.user.adjust.record.dao;

import com.yunzheng.studyLeaveSystem.modules.user.adjust.adjust.entity.Adjust;
import com.yunzheng.studyLeaveSystem.modules.user.adjust.record.entity.AdjustRecord;
import com.yunzheng.studyLeaveSystem.utils.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Author: YunZheng
 * @Date: 2023/01/29/12:31
 * @Description:
 */
public class RecordDao {

    /**
     * 查询调研学记录
     *
     * @param id
     * @return
     * @throws SQLException
     */
    public List<AdjustRecord> record(int id) throws SQLException {
        Connection connection = DBUtils.getCon();
        String sql = "select * from Adjust where id = ? and YEARWEEK(date_format(leave_date,'%Y-%m-%d')) = YEARWEEK(now())";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<AdjustRecord> records = new ArrayList<>();
        while (resultSet.next()) {
            String name = resultSet.getString("name");
            String gender = resultSet.getString("gender");
            String term = resultSet.getString("term");
            String direction = resultSet.getString("direction");
            String leave_date = resultSet.getString("leave_date");
            String leave_class = resultSet.getString("leave_class");
            String adjust_date = resultSet.getString("adjust_date");
            String adjust_class = resultSet.getString("adjust_class");
            String reason = resultSet.getString("reason");
            records.add(new AdjustRecord(name,gender, term, direction, leave_date, leave_class, adjust_date, adjust_class, reason));
        }
        return records;
    }

    /**
     * 取消调研学(遗留)
     * @param name
     * @param term
     * @param direction
     * @param leave_date
     * @return
     * @throws SQLException
     */
    public Boolean deleteAdjust(String name, String term, String direction, Date leave_date) throws SQLException {
        java.util.Date date = new java.util.Date();
        Date date1 = new Date(date.getTime());
        //当天的调研学不能取消,后期可以判断是否在研学之前
        if(date1.before(leave_date)){
            Connection connection = DBUtils.getCon();
            String sql = "delete from Adjust where name =? and term =? and direction =? and leave_date =?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, term);
            preparedStatement.setString(3, direction);
            preparedStatement.setDate(4, leave_date);
            int rows = preparedStatement.executeUpdate();
            if (rows > 0) {
                return true;
            }
        }
        return false;
    }
    /**
     * 查询调研学次数
     * @param id
     * @return
     * @throws Exception
     */
    public int quaryAdjustNum(int id) throws Exception {
        Connection connection = DBUtils.getCon();
        String sql = "select count(*) from adjust where id = ? and YEARWEEK(date_format(leave_date,'%Y-%m-%d')) = YEARWEEK(now())";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int anInt = resultSet.getInt(1);
            return anInt;
        }
        return 0;
    }

}
